/**
 * 
 */
package com.hezuti.common.utils;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Table;

import org.apache.commons.beanutils.BeanUtils;
import org.springframework.util.Assert;

/**
 * @author coldwater bean生成sql语句的方法
 */
public class SqlORMUtils {
	/**
	 * 灰长灰长简单的用beanClz生成Create Table Sql的方法，只支持int double string
	 * date类型，id为Key，其他报错
	 * 
	 * @param beanClz
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static String getCreateSql(Class beanClz) {

		Field[] fs = beanClz.getDeclaredFields();
		StringBuffer sb = new StringBuffer();
		String idField = "";
		String setKey = "";
		try {
			for (Field f : fs) {
				if (Modifier.isStatic(f.getModifiers()))
					continue;
				if (f.getAnnotation(Id.class) != null) {
					idField = f.getName() + " int not null,";
					setKey = "primary key (ID)";
					continue;
				}
				sb.append(f.getName()).append(" ").append(getFieldType(f.getType(), f.getAnnotation(Column.class))).append(",");
			}
			sb.insert(0, idField);
			sb.insert(0, "(");
			sb.insert(0, getTableName(beanClz));
			sb.insert(0, "create table ");

			if ("".equals(setKey))
				sb.setLength(sb.length() - 1);
			else
				sb.append(setKey);

			sb.append(")");
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
		return sb.toString();
	}

	/**
	 * 获得类中表明的表名，如果没有用类名
	 * 
	 * @param beanClz
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static String getTableName(Class beanClz) {
		String tablename = beanClz.getSimpleName();
		// 加表名
		if (beanClz.isAnnotationPresent(Table.class)) {
			Table t = (Table) beanClz.getAnnotation(Table.class);
			if (!"".equals(t.name()))
				tablename = t.name();
		}
		return tablename;
	}

	/**
	 * 获得Insert类型的Sql，适用于自增id 形如：insert into tableName(xxx,xxx,xxx)
	 * values(?,?,?);
	 * 
	 * @param beanClz
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static String getInsertSql(Class beanClz) {
		return getInsertSql(beanClz, null);
	}

	/**
	 * 获得Insert类型的Sql，适用于序列id 形如：insert into tableName(xxx,xxx,xxx)
	 * values(?,?,?);
	 * 
	 * @param beanClz
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static String getInsertSql(Class beanClz, String seqName) {
		Field[] fs = beanClz.getDeclaredFields();
		StringBuffer sb = new StringBuffer();
		StringBuffer sbFieldName = new StringBuffer();
		StringBuffer sbValue = new StringBuffer();
		try {
			for (Field f : fs) {
				if (f.getAnnotation(Id.class) != null) {
					if (seqName != null) {
						sbFieldName.append(f.getName()).append(",");
						sbValue.append(seqName).append(".nextval,");
					}
					continue;
				}
				sbFieldName.append(f.getName()).append(",");
				sbValue.append("?,");
			}
			sbValue.setLength(sbValue.length() - 1);
			sbFieldName.setLength(sbFieldName.length() - 1);
			sb.append("insert into ").append(getTableName(beanClz)).append(" (").append(sbFieldName).append(") values (").append(sbValue).append(")");
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
		return sb.toString();
	}

	@SuppressWarnings("unchecked")
	public static String getRemoveAllSql(Class beanClz) {
		StringBuffer sb = new StringBuffer();
		sb.append("delete from ").append(getTableName(beanClz));
		return sb.toString();
	}

	/**
	 * 获得删除数据的sql字符串
	 * 
	 * @param beanClz
	 * @param whereStr
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static String getRemoveSql(Class beanClz, String whereStr) {
		StringBuffer sb = new StringBuffer();
		try {
			sb.append("delete from ");//
			sb.append(getTableName(beanClz)).append(" ");
			if (whereStr != null)
				sb.append(whereStr);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
		return sb.toString();
	}

	/**
	 * 生成select语句
	 * 
	 * @param beanClz
	 * @param whereStr
	 * @param orderby
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static String getSelectSql(Class beanClz, String whereStr, String orderby) {
		return getSelectSql(beanClz, whereStr, orderby, null);
	}

	/**
	 * 生成select语句
	 * 
	 * @param beanClz
	 * @param whereStr
	 * @param orderby
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static String getSelectSql(Class beanClz, String whereStr, String orderby, String selectFields) {
		Field[] fs = beanClz.getDeclaredFields();
		StringBuffer sb = new StringBuffer();
		try {
			sb.append("select ");//
			if (selectFields == null || "".equals(selectFields.trim())) {
				for (Field f : fs) {
					sb.append(f.getName()).append(",");
				}
				sb.setLength(sb.length() - 1);
			} else {
				sb.append(selectFields);
			}

			sb.append(" from ").append(getTableName(beanClz)).append(" ");
			if (whereStr != null)
				sb.append(whereStr);
			if (orderby != null)
				sb.append(orderby);

		} catch (Exception e) {
			throw new RuntimeException(e);
		}
		return sb.toString();

	}

	/**
	 * 获得Update类型的Sql语句 形如：update tableName set xxx=?,xxx=? (请自行添加where部分)
	 * 
	 * @param beanClz
	 * @param whereStr
	 *            where字符串，如：”where id=?“，注：本方法内不包含"where"字符串
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static String getUpdateSql(Class beanClz, String whereStr) {
		return getUpdateSql(beanClz, whereStr, false);
	}

	/**
	 * 获得Update类型的Sql语句 形如：update tableName set xxx=?,xxx=? (请自行添加where部分)
	 * 
	 * @param beanClz
	 * @param whereStr
	 *            where字符串，如：”where id=?“，注：本方法内不包含"where"字符串
	 * @param skipUpdatable
	 *            是否忽略Annonation Column中的updatable说明
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static String getUpdateSql(Class beanClz, String whereStr, boolean skipUpdatable) {
		Field[] fs = beanClz.getDeclaredFields();
		StringBuffer sb = new StringBuffer();
		try {
			sb.append("update ").append(getTableName(beanClz)).append(" set ");
			for (Field f : fs) {
				// 跳过id字段
				if (f.getAnnotation(Id.class) != null) {
					continue;
				}
				if (!skipUpdatable) {
					// 跳过Column中有updatable=false的字段
					if (f.getAnnotation(Column.class) != null) {
						Column c = f.getAnnotation(Column.class);
						if (!c.updatable())
							continue;
					}
				}
				sb.append(f.getName()).append("=").append("?,");
			}
			sb.setLength(sb.length() - 1);
			sb.append(" ");
			if (whereStr != null)
				sb.append(whereStr);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
		return sb.toString();

	}

	@SuppressWarnings("unchecked")
	public static String getUpdateSql(Class beanClz) {
		return getUpdateSql(beanClz, null);
	}

	/**
	 * 数据类型转换
	 * 
	 * @param c
	 * @param ann
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	private static String getFieldType(Class c, Column ann) throws Exception {
		Assert.notNull(c);
		if (int.class.equals(c) || Integer.class.equals(c) || long.class.equals(c) || Long.class.equals(c)) {
			return "int";
		} else if (double.class.equals(c) || Double.class.equals(c)) {
			int length = 20;
			int scale = 3;
			if (ann != null && ann.length() > 0 && ann.length() != 255)
				length = ann.length();
			if (ann != null && ann.scale() > 0) {
				scale = ann.scale();
			}
			return "number(" + length + "," + scale + ")";
		} else if (String.class.equals(c)) {
			int length = 20;
			if (ann != null && ann.length() > 0)
				length = ann.length();
			return "varchar(" + length + ")";
		} else if (Date.class.equals(c)) {
			return "date";
		} else {
			throw new Exception(c.getName() + " type not support !");
		}
	}

	/**
	 * 用ResultSet生成对象
	 * 
	 * @param <T>
	 * @param beanClz
	 * @param m
	 * @param rs
	 * @return
	 * @throws IllegalAccessException
	 * @throws IllegalArgumentException
	 * @throws InstantiationException
	 * @throws InvocationTargetException
	 */
	@SuppressWarnings("unchecked")
	public static <T> T BuildResultSet(Class beanClz, ResultSet rs) throws SQLException, IllegalArgumentException, IllegalAccessException,
			InstantiationException, InvocationTargetException {
		T result = (T) beanClz.newInstance();

		Field[] fs = beanClz.getDeclaredFields();
		for (Field f : fs) {

			// Class c = f.getType();
			BeanUtils.setProperty(result, f.getName(), rs.getObject(f.getName()));
			// if (int.class.equals(c) || Integer.class.equals(c)) {
			// BeanUtils.setProperty(result,
			// f.getName(),rs.getInt(f.getName()));
			//
			// } else if (long.class.equals(c) || Long.class.equals(c)) {
			// f.setLong(result, rs.getLong(f.getName()));
			// } else if (double.class.equals(c) || Double.class.equals(c)) {
			// f.setDouble(result, rs.getBigDecimal(f.getName()).doubleValue());
			// } else if (String.class.equals(c)) {
			// f.set(result, rs.getString(f.getName()));
			// } else if (Date.class.equals(c)) {
			// f.set(result, new Date(rs.getDate(f.getName()).getTime()));
			// } else {
			// throw new RuntimeException(c.getName() + "type not support !");
			// }
			// System.out.println(m.get(f.getName())+",");
		}
		return result;
	}

	/**
	 * 生成ps
	 * 
	 * @param beanClz
	 *            要操作的类
	 * @param m
	 *            要写入的数据Map
	 * @param ps
	 *            ps对象
	 * 
	 */
	@SuppressWarnings("unchecked")
	public static int BuildPreparedStatement(Class beanClz, Map m, PreparedStatement ps, SqlType sqlType) throws SQLException {
		return BuildPreparedStatement(beanClz, m, ps, sqlType, false);
	}

	/**
	 * 生成ps
	 * 
	 * @param beanClz
	 *            要操作的类
	 * @param m
	 *            要写入的数据Map
	 * @param ps
	 *            ps对象
	 * @param skipInsertableOrUpdatable
	 *            是否跳过Annonation insertable 或者 updateable
	 */
	@SuppressWarnings("unchecked")
	public static int BuildPreparedStatement(Class beanClz, Map m, PreparedStatement ps, SqlType sqlType, boolean skipInsertableOrUpdatable)
			throws SQLException {
		int i = 1;
		Field[] fs = beanClz.getDeclaredFields();
		for (Field f : fs) {
			if (f.getAnnotation(Id.class) != null) {
				continue;
			}
			if (!skipInsertableOrUpdatable) {
				// 跳过“禁止insert or update”的字段
				Column column = f.getAnnotation(Column.class);
				if (column != null) {
					if (SqlType.INSERT.equals(sqlType) && (!column.insertable()))
						continue;
					if (SqlType.UPDATE.equals(sqlType) && (!column.updatable()))
						continue;
				}
			}
			if (m.get(f.getName()) == null)
				ps.setNull(i++, java.sql.Types.NULL);
			else {
				Class c = f.getType();

				if (int.class.equals(c) || Integer.class.equals(c)) {
					ps.setInt(i++, ((Number) m.get(f.getName())).intValue());
				} else if (long.class.equals(c) || Long.class.equals(c)) {
					ps.setLong(i++, ((Number) m.get(f.getName())).longValue());
				} else if (double.class.equals(c) || Double.class.equals(c)) {
					Object o = m.get(f.getName());
					if (o instanceof BigDecimal)
						ps.setBigDecimal(i++, (BigDecimal) o);
					else
						ps.setDouble(i++, (Double) o);
				} else if (String.class.equals(c)) {
					ps.setString(i++, m.get(f.getName()).toString());
				} else if (Date.class.equals(c)) {
					ps.setTimestamp(i++, new java.sql.Timestamp(((Date) m.get(f.getName())).getTime()));
				} else {
					throw new RuntimeException(c.getName() + "type not support !");
				}
			}
			// System.out.println(m.get(f.getName())+",");
		}
		return i;
	}

	/**
	 * bean转Map，保持类型,以属性名为key，属性值为value
	 * 
	 * @param beanClz
	 * @param bean
	 * @return
	 * @throws IllegalAccessException
	 * @throws IllegalArgumentException
	 * @throws InvocationTargetException
	 * @throws IllegalAccessException
	 * @throws IllegalArgumentException
	 */
	@SuppressWarnings("unchecked")
	public static Map bean2Map(Class beanClz, Object bean) throws InvocationTargetException, IllegalArgumentException, IllegalAccessException {
		Method[] fs = beanClz.getMethods();
		Map result = new HashMap();
		for (Method f : fs) {
			if (f.getModifiers() != Modifier.STATIC + Modifier.PUBLIC) {
				if (f.getName().indexOf("get") == 0) {
					Object v = f.invoke(bean);
					result.put(f.getName().substring(3).substring(0, 1).toLowerCase().concat(f.getName().substring(4)), v);
				}
			}
		}
		return result;
	}

	/**
	 * id拼接字符串,如：1,2,3,4
	 * 
	 * @param l
	 * @return
	 */
	public static String listToIds(Collection<String> l) {
		StringBuffer sb = new StringBuffer();
		for (String id : l) {
			sb.append("'").append(id).append("',");
		}
		if (sb.length() > 0)
			sb.delete(sb.length() - 1, sb.length());
		return sb.toString();
	}

}
